--Execute as OE

CREATE OR REPLACE PROCEDURE write_lob (p_file IN VARCHAR2)
IS
  i    NUMBER;   v_id NUMBER;  v_b  BLOB;
BEGIN
  DBMS_OUTPUT.ENABLE;
  DBMS_OUTPUT.PUT_LINE('Begin inserting rows...');
  FOR i IN 1 .. 5 LOOP
    v_id:=SUBSTR(p_file, 1, 4);
    INSERT INTO product_descriptions 
      VALUES (v_id, EMPTY_BLOB())
      RETURNING detailed_product_info INTO v_b;
    loadLOBFromBFILE_proc(v_b,p_file);
    DBMS_OUTPUT.PUT_LINE('Row '|| i ||' inserted.');
  END LOOP;
  COMMIT;
END write_lob;
/
